--POUR CONTROLE

/*remise dans le bon sens de masque-var*/
-- masque_var_trans;


define erp='erp_mli2.';
define outils='outils_mli2.';

 --CALL uvse_ExistsOrCreate('GEN_CHAMPCTAV');

 DELETE FROM se_GEN_CHAMPCTAV;

 /*
select * from (
SELECT &outils.stragg(source) source,champsais_cod, champctav_typ, enreg_id, grille_cod, bornemsg_lib, complsqlgrille_lib, complsqltable_lib, entitecle_cod, entiteent_cod, entiteslig_cod, grillechamp_cod, enreg_s, formatcol_val, msg_lib, borne_typ, borneinf_val, bornesup_val, valeur_typ, msgvaleur_lib
FROM (
  SELECT 'uv' source ,g.* FROM se_GEN_CHAMPCTAV g
  UNION ALL
  SELECT 'se' || v.vrt_cod || v.att_cod
      -- || (select &outils.stragg(valeur_val) from &erp.gen_champctavval vv where vv.champsais_cod=g.champsais_cod)
      source ,g.* FROM &erp.GEN_CHAMPCTAV g,&erp.vrt_att v where v.champsais_cod=g.champsais_cod
  )

GROUP BY champsais_cod, champctav_typ, enreg_id, grille_cod, bornemsg_lib, complsqlgrille_lib, complsqltable_lib, entitecle_cod, entiteent_cod, entiteslig_cod, grillechamp_cod, enreg_s, formatcol_val, msg_lib, borne_typ, borneinf_val, bornesup_val, valeur_typ, msgvaleur_lib
HAVING Count(1)!=2 ) r
ORDER BY CHAMPCTAV_TYP,champsais_cod,source  */



 INSERT INTO se_GEN_CHAMPCTAV
 (SELECT distinct v.champsais_cod,
         Decode(p.x,0,'C','A') champctav_typ,
         0 enreg_id,
         Decode(InStr(m.col22,'.MOLI'),0,NULL,Decode(p.x,0,REPLACE(m.col22,'.MOLI',''),null)) grille_cod,
         NULL bornemsg_lib,
         NULL complsqlgrille_lib,
         NULL complsqltable_lib,
         null entitecle_cod,
         Decode(InStr(m.col22,'.MOLI'),0,Decode(p.x,0,m.col22,null),null) entiteent_cod,
         null entiteslig_cod,
         Decode(InStr(m.col22,'.MOLI'),0,NULL,Decode(p.x,0,Decode(m.col22,NULL,NULL,'GRILLESTD_LIB'),null)) grillechamp_cod,
         'N' enreg_s,
         NULL formatcol_val,
         NULL msg_lib,
         0 borne_typ,
         Decode(col32,'STANDARD',Decode(p.x,0,SubStr(col07,0,InStr(col07,'<')-1),NULL),NULL) borneinf_val,
         Decode(col32,'STANDARD',Decode(p.x,0,SubStr(col07,InStr(col07,'>')+1,99),NULL),NULL)  bornesup_val,
         'A' valeur_typ,
         NULL msgvaleur_lib
      FROM  masque_var_trans m , se_VRT_ATT v,&outils.pivot p
     WHERE col05 IS NOT NULL AND Length(m.id)<=5
       AND v.vrt_cod(+)=m.id AND v.att_cod(+)=m.col05
       AND p.x<2
     );


 UPDATE se_GEN_CHAMPCTAV tf SET enreg_id=Nvl((select enreg_id FROM &erp.GEN_CHAMPCTAV f WHERE f.champsais_cod=tf.champsais_cod and f.CHAMPCTAV_TYP=tf.CHAMPCTAV_TYP),0) ;
 UPDATE se_GEN_CHAMPCTAV tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;

 UPDATE se_GEN_CHAMPCTAV tf SET ENTITEENT_COD='ART_COLLECTION', ENTITECLE_COD='COLLECTION_COD',ENTITESLIG_COD='COLLECTION_LIB' WHERE  ENTITEENT_COD='COLLECTIONS' ;
 UPDATE se_GEN_CHAMPCTAV tf SET ENTITEENT_COD='ART_LIGNE', ENTITECLE_COD='LIGNE_COD',ENTITESLIG_COD='LIGNE_LIB' WHERE  ENTITEENT_COD='LIGNES' ;
 UPDATE se_GEN_CHAMPCTAV tf SET ENTITEENT_COD='ACH_FOURNISSEUR', ENTITECLE_COD='FOURNISSEUR_COD',ENTITESLIG_COD='AFF_NOM' WHERE  ENTITEENT_COD='FOURNISSEURS' ;


UPDATE se_GEN_CHAMPCTAV SET GRILLE_COD='LON_GENERIQUE' ,GRILLECHAMP_COD='LONGPAT_COD' WHERE ENREG_ID =134812  ;
UPDATE se_GEN_CHAMPCTAV SET GRILLE_COD='TAI_GENERIQUE' ,GRILLECHAMP_COD='TAILLEPAT_COD' WHERE ENREG_ID = 135737 ;

 drop table se_GEN_CHAMPCTAV_vrtatt;
 create table se_GEN_CHAMPCTAV_vrtatt as (select * from se_GEN_CHAMPCTAV);

 commit;





